#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
小红书数据管理系统 - 数据库API接口
提供RESTful风格的接口，用于对数据库进行增删改查操作
"""

from flask import Blueprint, request, jsonify, g
from api.db_sqlite_manager import XHSDataDB
import datetime
import json
import os

# 创建蓝图
db_api = Blueprint('db_api', __name__, url_prefix='/api')

# 使用Flask的g对象存储线程特定的数据库连接
def get_db():
    """获取数据库连接 - 每个请求一个新连接以确保线程安全"""
    if 'xhs_db' not in g:
        # 获取应用根目录
        app_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        db_path = os.path.join(app_root, 'xhs_database.db')
        g.xhs_db = XHSDataDB(db_path)
    return g.xhs_db

# 请求结束时关闭数据库连接
@db_api.teardown_app_request
def close_db(exception):
    """请求结束时关闭数据库连接"""
    db = g.pop('xhs_db', None)
    if db is not None:
        db.close()

###########################################
# 采集任务相关API
###########################################

@db_api.route('/tasks', methods=['GET'])
def get_tasks():
    """获取所有采集任务"""
    db = get_db()
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 20))
    offset = (page - 1) * limit
    
    # 获取任务列表
    tasks = db.get_all_tasks(limit=limit, offset=offset)
    
    # 获取任务总数
    db.cursor.execute("SELECT COUNT(*) as count FROM collection_tasks")
    total = db.cursor.fetchone()['count']
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'count': total,
        'data': tasks
    })

@db_api.route('/tasks/<int:task_id>', methods=['GET'])
def get_task(task_id):
    """获取指定ID的采集任务"""
    db = get_db()
    task = db.get_task_by_id(task_id)
    
    if task:
        return jsonify({
            'code': 0,
            'msg': '获取成功',
            'data': task
        })
    else:
        return jsonify({
            'code': 404,
            'msg': '任务不存在',
            'data': None
        }), 404

@db_api.route('/tasks', methods=['POST'])
def add_task():
    """添加新的采集任务"""
    data = request.get_json()
    
    # 检查必填字段
    if not data or 'keyword' not in data:
        return jsonify({
            'code': 400,
            'msg': '缺少必填字段',
            'data': None
        }), 400
    
    # 补充默认值
    task_data = {
        'keyword': data['keyword'],
        'start_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'target_notes_count': data.get('target_notes_count', 100),
        'target_comments_count': data.get('target_comments_count', 1000),
        'actual_notes_count': 0,
        'actual_comments_count': 0,
        'status': '等待中',
    }
    
    # 添加任务
    db = get_db()
    task_id = db.add_task(task_data)
    
    # 获取新添加的任务
    task = db.get_task_by_id(task_id)
    
    return jsonify({
        'code': 0,
        'msg': '任务创建成功',
        'data': task
    }), 201

@db_api.route('/tasks/<int:task_id>', methods=['PUT'])
def update_task(task_id):
    """更新采集任务"""
    data = request.get_json()
    
    if not data:
        return jsonify({
            'code': 400,
            'msg': '请提供更新数据',
            'data': None
        }), 400
    
    # 获取原任务
    db = get_db()
    task = db.get_task_by_id(task_id)
    
    if not task:
        return jsonify({
            'code': 404,
            'msg': '任务不存在',
            'data': None
        }), 404
    
    # 执行更新
    success = db.update_task(task_id, data)
    
    if success:
        updated_task = db.get_task_by_id(task_id)
        return jsonify({
            'code': 0,
            'msg': '更新成功',
            'data': updated_task
        })
    else:
        return jsonify({
            'code': 500,
            'msg': '更新失败',
            'data': None
        }), 500

@db_api.route('/tasks/<int:task_id>', methods=['DELETE'])
def delete_task(task_id):
    """删除采集任务"""
    db = get_db()
    
    # 检查任务是否存在
    task = db.get_task_by_id(task_id)
    if not task:
        return jsonify({
            'code': 404,
            'msg': '任务不存在',
            'data': None
        }), 404
    
    # 执行删除
    success = db.delete_task(task_id)
    
    if success:
        return jsonify({
            'code': 0,
            'msg': '删除成功',
            'data': None
        })
    else:
        return jsonify({
            'code': 500,
            'msg': '删除失败',
            'data': None
        }), 500

###########################################
# 笔记相关API
###########################################

@db_api.route('/notes', methods=['GET'])
def get_notes():
    """获取所有笔记"""
    db = get_db()
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 20))
    keyword = request.args.get('keyword', '')
    offset = (page - 1) * limit
    
    # 获取笔记列表
    notes = db.get_all_notes(limit=limit, offset=offset, keyword=keyword)
    
    # 获取笔记总数
    query = "SELECT COUNT(*) as count FROM notes"
    params = []
    if keyword:
        query += " WHERE keyword LIKE ?"
        params.append(f"%{keyword}%")
    
    db.cursor.execute(query, tuple(params))
    total = db.cursor.fetchone()['count']
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'count': total,
        'data': notes
    })

@db_api.route('/notes/<int:note_id>', methods=['GET'])
def get_note(note_id):
    """获取指定ID的笔记"""
    db = get_db()
    note = db.get_note_by_id(note_id)
    
    if note:
        # 获取笔记相关的评论
        comments = db.get_comments_by_note(note_id, limit=50)
        
        # 合并数据
        note_with_comments = dict(note)
        note_with_comments['comments'] = comments
        
        return jsonify({
            'code': 0,
            'msg': '获取成功',
            'data': note_with_comments
        })
    else:
        return jsonify({
            'code': 404,
            'msg': '笔记不存在',
            'data': None
        }), 404

@db_api.route('/notes', methods=['POST'])
def add_note():
    """添加新的笔记"""
    data = request.get_json()
    
    # 检查必填字段
    if not data or 'title' not in data or 'author' not in data:
        return jsonify({
            'code': 400,
            'msg': '缺少必填字段',
            'data': None
        }), 400
    
    # 补充采集时间
    if 'collect_time' not in data:
        data['collect_time'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    # 添加笔记
    db = get_db()
    try:
        note_id = db.add_note(data)
        note = db.get_note_by_id(note_id)
        
        return jsonify({
            'code': 0,
            'msg': '笔记添加成功',
            'data': note
        }), 201
    except Exception as e:
        return jsonify({
            'code': 500,
            'msg': f'添加失败: {str(e)}',
            'data': None
        }), 500

###########################################
# 用户相关API
###########################################

@db_api.route('/users', methods=['GET'])
def get_users():
    """获取所有用户"""
    db = get_db()
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 20))
    search = request.args.get('search', '')
    offset = (page - 1) * limit
    
    # 获取用户列表
    users = db.get_all_users(limit=limit, offset=offset, search=search)
    
    # 获取用户总数
    count_query = "SELECT COUNT(*) as count FROM users"
    count_params = []
    
    if search:
        count_query += " WHERE user_name LIKE ? OR location LIKE ?"
        count_params.extend([f"%{search}%", f"%{search}%"])
    
    db.cursor.execute(count_query, tuple(count_params))
    total = db.cursor.fetchone()['count']
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'count': total,
        'data': users
    })

@db_api.route('/users/<user_id>', methods=['GET'])
def get_user(user_id):
    """获取指定ID的用户"""
    db = get_db()
    user = db.get_user_by_id(user_id)
    
    if user:
        return jsonify({
            'code': 0,
            'msg': '获取成功',
            'data': user
        })
    else:
        return jsonify({
            'code': 404,
            'msg': '用户不存在',
            'data': None
        }), 404

###########################################
# 位置相关API
###########################################

@db_api.route('/locations', methods=['GET'])
def get_locations():
    """获取所有位置"""
    db = get_db()
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 20))
    search = request.args.get('search', '')
    offset = (page - 1) * limit
    
    # 获取位置列表
    locations = db.get_all_locations(limit=limit, offset=offset, search=search)
    
    # 获取位置总数
    count_query = "SELECT COUNT(*) as count FROM locations"
    count_params = []
    
    if search:
        count_query += " WHERE location_name LIKE ? OR province LIKE ? OR city LIKE ?"
        count_params.extend([f"%{search}%", f"%{search}%", f"%{search}%"])
    
    db.cursor.execute(count_query, tuple(count_params))
    total = db.cursor.fetchone()['count']
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'count': total,
        'data': locations
    })

@db_api.route('/locations/<int:location_id>', methods=['GET'])
def get_location(location_id):
    """获取指定ID的位置"""
    db = get_db()
    db.cursor.execute("SELECT * FROM locations WHERE location_id = ?", (location_id,))
    row = db.cursor.fetchone()
    
    if row:
        location = dict(row)
        
        # 获取该位置的用户
        db.cursor.execute(
            "SELECT * FROM users WHERE location = ? ORDER BY followers_count DESC LIMIT 10", 
            (location['location_name'],)
        )
        users = [dict(row) for row in db.cursor.fetchall()]
        
        # 获取该位置的评论
        db.cursor.execute(
            "SELECT * FROM comments WHERE location = ? ORDER BY comment_time DESC LIMIT 20", 
            (location['location_name'],)
        )
        comments = [dict(row) for row in db.cursor.fetchall()]
        
        # 合并数据
        location_with_details = location
        location_with_details['users'] = users
        location_with_details['comments'] = comments
        
        return jsonify({
            'code': 0,
            'msg': '获取成功',
            'data': location_with_details
        })
    else:
        return jsonify({
            'code': 404,
            'msg': '位置不存在',
            'data': None
        }), 404

###########################################
# 统计数据API
###########################################

@db_api.route('/statistics/dashboard', methods=['GET'])
def get_dashboard_statistics():
    """获取仪表盘统计数据"""
    db = get_db()
    stats = db.get_dashboard_statistics()
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'data': stats
    })

@db_api.route('/statistics/keywords', methods=['GET'])
def get_keyword_statistics():
    """获取关键词统计数据"""
    db = get_db()
    stats = db.get_keyword_statistics()
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'data': stats
    })

@db_api.route('/statistics/locations', methods=['GET'])
def get_location_statistics():
    """获取位置统计数据"""
    db = get_db()
    stats = db.get_location_statistics()
    
    return jsonify({
        'code': 0,
        'msg': '获取成功',
        'data': stats
    }) 